drop table if exists jms_dm.dm_waybill_false_taking_network_dt;
CREATE TABLE if not exists jms_dm.dm_waybill_false_taking_network_dt(
    dt                                 date     NOT NULL COMMENT '分区日期'
    ,taking_date                       date     NOT NULL COMMENT '揽收日期'
    ,receive_virt_agent_code           varchar(255) NULL COMMENT '虚拟收件代理区code'
    ,receive_agent_code                varchar(255) NULL COMMENT '收件代理区code'
    ,receive_network_code              varchar(255) NULL comment '收件网点code'
    ,receive_virt_agent_name           varchar(255) NULL COMMENT '虚拟收件代理区名称'
    ,receive_agent_name                varchar(255) NULL COMMENT '收件代理区名称'
    ,receive_network_name              varchar(255) NULL comment '收件网点名称'
    ,qxt_false_taking_cnt              bigint       NULL COMMENT '七星潭虚假揽收票数'
    ,thd_false_taking_cnt              bigint       NULL COMMENT '桃花岛虚假揽收票数'
    ,xyf_false_taking_cnt              bigint       NULL COMMENT '逍遥峰虚假揽收票数'
    ,zjs_false_taking_cnt              bigint       NULL COMMENT '紫金山虚假揽收票数'
    ,receive_network_false_taking_cnt             bigint comment '虚假揽收票数'
    ,receive_network_should_hand_in_cnt           bigint comment '网点全部应交件票数'
    ,receive_network_platform_should_hand_in_cnt  bigint comment '网点四大平台应交件票数'
    ,network_qxt_should_hand_in_cnt               bigint comment '七星潭应交件票数'
    ,network_thd_should_hand_in_cnt               bigint comment '桃花岛应交件票数'
    ,network_xyf_should_hand_in_cnt               bigint comment '逍遥峰应交件票数'
    ,network_zjs_should_hand_in_cnt               bigint comment '紫金山应交件票数'
)
ENGINE=olap
DUPLICATE KEY(dt, taking_date, receive_virt_agent_code, receive_agent_code, receive_network_code)
comment '虚假揽收日报-网点汇总'
PARTITION BY RANGE (taking_date) (
    START ("2023-06-15") END ("2023-07-10") EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(receive_network_code) BUCKETS 3
PROPERTIES (
    "replication_num" = "3",
    "dynamic_partition.enable" = "true",
    "dynamic_partition.time_unit" = "DAY",
    "dynamic_partition.time_zone" = "Asia/Shanghai",
    "dynamic_partition.start" = "-185",
    "dynamic_partition.end" = "3",
    "dynamic_partition.prefix" = "p",
    "dynamic_partition.buckets" = "3",
    "in_memory" = "false",
    "storage_format" = "V2"
);